package com.example.demo.repository;

import com.example.demo.dto.DownloadPrintFileListDto;
import com.example.demo.dto.NewPrintFileDto;
import com.example.demo.dto.PrintFileDto;
import com.example.demo.dto.PrintFileListDto;
import com.example.demo.mode.PrintFile;
import com.example.demo.mode.PrintFileOperation;
import com.example.demo.mode.PrintFilesSub;
import org.apache.ibatis.annotations.*;

import java.util.List;


/**
 * @author 21024
 */
@Mapper
public interface PrintFileMapper {
    @Select("SELECT TOP 20 " +
            "PartNumber AS partNumber, " +
            "PartName AS partName, " +
            "LastUpdateTime AS time, " +
            "LastUpdateUserName AS userName, " +
            "FileStatus AS status " +
            "FROM printFiles " +
            "ORDER BY LastUpdateTime DESC")
    List<PrintFileListDto> printFileList();

    @Select("SELECT " +
            "PartNumber AS partNumber, " +
            "PartName AS partName, " +
            "LastUpdateTime AS time, " +
            "LastUpdateUserName AS userName, " +
            "FileStatus AS status " +
            "FROM printFiles " +
            "WHERE partNumber LIKE CONCAT('%', #{partNumber}, '%') ")
    List<PrintFileListDto> findByPartNumber(String partNumber);


    @Select("SELECT " +
            "PartNumber AS partNumber, " +
            "PartName AS partName, " +
            "LastUpdateTime AS time, " +
            "LastUpdateUserName AS userName, " +
            "FileStatus AS status " +
            "FROM printFiles " +
            "WHERE PartName LIKE CONCAT('%', #{fileName}, '%') ")
    List<PrintFileListDto> findByFileName(String fileName);

    @Select("SELECT * FROM printFiles WHERE PartNumber = #{partNumber}")
    PrintFileDto selectByPartNumber(String partNumber);

    @Select("WITH RankedParts AS (\n" +
            "    SELECT *,\n" +
            "           ROW_NUMBER() OVER (PARTITION BY PartNumber, SubPartName ORDER BY SubID DESC) AS rn\n" +
            "    FROM printFilesSub\n" +
            "    WHERE PartNumber = #{partNumber}\n" +
            ")\n" +
            "SELECT * FROM RankedParts WHERE rn = 1;")
    List<PrintFilesSub> selectByPartNumberSub(String partNumber);

    @Select("select b.FileID,LastUpdateUserID,LastUpdateTime,Description,LastUpdateUserName " +
            "from printFilesSub b " +
            "left join dbo.printFiles pF on b.FileID = pF.FileID " +
            "where b.FileName LIKE CONCAT('%', #{fileName}, '%')")
    PrintFile selectByFileName(String fileName);

    @Update("<script>" +
            "UPDATE printFiles SET " +
            "LastUpdateTime = #{lastUpdateTime}, " +
            "LastUpdateUserName = #{lastUpdateUserName}, " +
            "LastUpdateUserID = #{lastUpdateUserId}, " +
            "Version = #{version} ," +
            "FileStatus = 0 " +
            "<if test='description != null'>,Description = #{description}</if> " +
            "WHERE partNumber = #{partNumber}" +
            "</script>")
    Boolean update(PrintFile printFile);

    @Insert("INSERT INTO printFiles (PartNumber, PartName, LastUpdateTime, LastUpdateUserName, LastUpdateUserID,  Version, Description, PartType,FileStatus,CreateTime,CreateUserID,CreateUserName) VALUES" +
            " (#{partNumber}, #{partName}, #{createTime}, #{createUserName}, #{createUserId},  #{version}, #{description}, #{partType},0,#{createTime},#{createUserId},#{createUserName})")
    @Options(useGeneratedKeys = true, keyProperty = "fileId")
    void insert(NewPrintFileDto printFile);

    @Select("SELECT COUNT(*) FROM printFiles WHERE partNumber = #{partNumber}")
    int selectPartNumber(String partNumber);


    @Insert("INSERT INTO printFileOperations (FileId, FileName, UserID, UserName, OperationType, OperationTime, IPAddress, Remarks) VALUES" +
            " (#{fileId},#{fileName},#{userId},#{userName},#{operationType},#{operationTime},#{ipAddress},#{remarks})")
    boolean insertOperation(PrintFileOperation printFileOperation);

    @Insert("INSERT INTO printFilesSub (FileID, PartNumber, SubPartName, FileName) VALUES " +
            "(#{fileId},#{partNumber},#{subPartName},#{fileName})")
    boolean insertSub(PrintFilesSub printFilesSub);

    @Update("UPDATE printFilesSub SET FileName = #{fileName} WHERE PartNumber = #{partNumber} AND SubPartName = #{subPartName}")
    boolean updateSub(String fileName, String partNumber, String subPartName);

    @Select("WITH RankedParts AS (\n" +
            "select  printFiles.PartNumber,FileStatus,Version,SubPartName,FileName,LastUpdateUserName,LastUpdateTime,\n" +
            "        ROW_NUMBER() OVER (PARTITION BY printFiles.PartNumber, SubPartName ORDER BY SubID DESC) AS rn\n" +
            "from printFiles\n" +
            "    left join dbo.printFilesSub pFS on printFiles.FileID = pFS.FileID\n" +
            "where printFiles.PartNumber = 36001176)\n" +
            "SELECT * FROM RankedParts WHERE rn = 1")
    List<DownloadPrintFileListDto> downloadPrintFileList(String partNumber);
}
